const DatabaseManager = require('../database/database');

class JournalPaperService {
  constructor(dbManager = null) {
    // 如果传入了数据库管理器实例，使用它；否则创建新实例
    this.db = dbManager || new DatabaseManager();
  }

  /**
   * 创建新的期刊论文记录
   * @param {Object} paperData - 论文数据
   * @param {string} paperData.title - 论文标题
   * @param {string} paperData.author - 作者
   * @param {string} paperData.author_ranking - 作者排名
   * @param {string} paperData.keywords - 关键词
   * @param {string} paperData.journal_name - 期刊名称
   * @param {string} paperData.journal_abbreviation - 期刊缩写
   * @param {string} paperData.publication_date - 发表日期
   * @param {string} paperData.volume_number - 卷号
   * @param {string} paperData.page_numbers - 页码
   * @param {string} paperData.jcr_quartile - JCR分区
   * @param {string} paperData.sci_quartile - SCI分区
   * @param {string} paperData.other_level - 其他级别
   * @param {string} paperData.field_of_expertise - 专业领域
   * @param {string} paperData.impact_factor - 影响因子
   * @param {number} paperData.number_of_citations - 引用次数
   * @param {string} paperData.paper_file_url - 论文文件URL
   * @param {string} paperData.attachment_url - 附件URL
   * @returns {Promise<number>} 新论文记录的ID
   */
  async createPaper(paperData) {
    try {
      // 验证必填字段（注意：paper_file_url 和 attachment_url 可以暂时为空，在保存文件后填充）
      const requiredFields = [
        'title', 'author', 'author_ranking', 'keywords', 'journal_name',
        'journal_abbreviation', 'publication_date', 'volume_number',
        'page_numbers', 'jcr_quartile', 'sci_quartile', 'other_level',
        'field_of_expertise', 'impact_factor', 'number_of_citations'
        // paper_file_url 和 attachment_url 可以暂时为空
      ];

      for (const field of requiredFields) {
        if (!paperData[field]) {
          throw new Error(`${field} 字段不能为空`);
        }
      }

      // 验证日期格式
      if (paperData.publication_date && !this.isValidDate(paperData.publication_date)) {
        throw new Error('发表日期格式不正确，请使用 YYYY-MM-DD 格式');
      }

      // 验证引用次数为数字
      if (paperData.number_of_citations && isNaN(Number(paperData.number_of_citations))) {
        throw new Error('引用次数必须为数字');
      }

      const paper = {
        uid: paperData.uid || null, // 用户ID（如果提供）
        title: paperData.title,
        author: paperData.author,
        author_ranking: paperData.author_ranking,
        keywords: paperData.keywords,
        journal_name: paperData.journal_name,
        journal_abbreviation: paperData.journal_abbreviation,
        publication_date: paperData.publication_date,
        volume_number: paperData.volume_number,
        page_numbers: paperData.page_numbers,
        jcr_quartile: paperData.jcr_quartile,
        sci_quartile: paperData.sci_quartile,
        other_level: paperData.other_level,
        field_of_expertise: paperData.field_of_expertise,
        impact_factor: paperData.impact_factor,
        number_of_citations: Number(paperData.number_of_citations),
        // 确保文件 URL 字段至少是空字符串，避免 undefined 导致 NOT NULL 约束错误
        paper_file_url: paperData.paper_file_url || '',
        attachment_url: paperData.attachment_url || ''
      };

      const paperId = await this.db.insert('journal_paper_info', paper);
      return paperId;
    } catch (error) {
      console.error('创建期刊论文记录失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID获取期刊论文
   * @param {number} paperId - 论文ID
   * @returns {Promise<Object|null>} 论文信息
   */
  async getPaperById(paperId) {
    try {
      const paper = await this.db.findById('journal_paper_info', paperId);
      return paper;
    } catch (error) {
      console.error('获取期刊论文失败:', error);
      throw error;
    }
  }

  /**
   * 获取所有期刊论文
   * @param {Object} filters - 过滤条件
   *   - searchKeyword: 搜索关键词（可选，用于在多个字段中模糊搜索）
   *   - 其他字段: 用于等值查询
   * @param {Object} pagination - 分页参数 { limit, offset }
   * @returns {Promise<Object>} { papers: Array, total: number }
   */
  async getAllPapers(filters = {}, pagination = null) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        // 分离搜索关键词和筛选条件
        const { searchKeyword, ...filterConditions } = filters;
        
        let sql = 'SELECT * FROM journal_paper_info';
        let countSql = 'SELECT COUNT(*) as total FROM journal_paper_info';
        const values = [];
        const conditions = [];

        // 构建 WHERE 子句
        // 1. 处理筛选条件（支持等值查询和范围查询）
        // 支持范围查询的字段列表（数字类型或可转换为数字的字段）
        const numericFields = ['number_of_citations', 'impact_factor'];
        
        Object.keys(filterConditions).forEach(key => {
          const value = filterConditions[key];
          if (value !== null && value !== undefined && value !== '') {
            const valueStr = String(value).trim();
            const isNumericField = numericFields.includes(key);
            
            // 检查是否是范围查询（>=, <=, >, <, =），仅对数字字段支持
            if (isNumericField && /^[<>]=?/.test(valueStr)) {
              // 范围查询：提取操作符和数值
              const match = valueStr.match(/^(>=|<=|>|<|=)(.+)$/);
              if (match) {
                const operator = match[1];
                const numValue = match[2];
                // 尝试解析为数字，如果成功则进行数值比较
                const num = parseFloat(numValue);
                if (!isNaN(num)) {
                  // 对于 TEXT 类型的字段（如 impact_factor），使用 CAST 转换为数字
                  if (key === 'impact_factor') {
                    conditions.push(`CAST(${key} AS REAL) ${operator} ?`);
                  } else {
                    conditions.push(`${key} ${operator} ?`);
                  }
                  values.push(num);
                } else {
                  // 如果不是数字，使用等值查询
                  conditions.push(`${key} = ?`);
                  values.push(value);
                }
              } else {
                // 格式不正确，使用等值查询
                conditions.push(`${key} = ?`);
                values.push(value);
              }
            } else {
              // 普通等值查询
              conditions.push(`${key} = ?`);
              values.push(value);
            }
          }
        });

        // 2. 处理搜索关键词（模糊搜索）
        if (searchKeyword && searchKeyword.trim() !== '') {
          const searchPattern = `%${searchKeyword.trim()}%`;
          const searchConditions = [
            'title LIKE ?',
            'author LIKE ?',
            'keywords LIKE ?',
            'journal_name LIKE ?',
            'field_of_expertise LIKE ?'
          ];
          conditions.push(`(${searchConditions.join(' OR ')})`);
          // 为每个搜索字段添加搜索模式
          values.push(...[searchPattern, searchPattern, searchPattern, searchPattern, searchPattern]);
        }

        // 组合 WHERE 子句
        if (conditions.length > 0) {
          const whereClause = conditions.join(' AND ');
          sql += ` WHERE ${whereClause}`;
          countSql += ` WHERE ${whereClause}`;
        }

        // 添加排序（按发表日期倒序）
        sql += ' ORDER BY publication_date DESC';

        // 添加分页
        if (pagination && pagination.limit) {
          sql += ' LIMIT ?';
          values.push(pagination.limit);
          
          if (pagination.offset) {
            sql += ' OFFSET ?';
            values.push(pagination.offset);
          }
        }

        // 构建计数查询的参数（排除分页参数）
        let countValues = [...values];
        if (pagination) {
          if (pagination.offset) {
            countValues = countValues.slice(0, -2); // 移除 LIMIT 和 OFFSET 参数
          } else if (pagination.limit) {
            countValues = countValues.slice(0, -1); // 移除 LIMIT 参数
          }
        }

        // 执行查询
        this.db.db.all(sql, values, (err, rows) => {
          if (err) {
            console.error('查询期刊论文失败:', err);
            reject(err);
            return;
          }

          // 查询总数（不使用分页参数）
          this.db.db.get(countSql, countValues, (countErr, countRow) => {
            if (countErr) {
              console.error('查询期刊论文总数失败:', countErr);
              reject(countErr);
              return;
            }

            resolve({
              papers: rows || [],
              total: countRow ? countRow.total : 0
            });
          });
        });
      });
    } catch (error) {
      console.error('获取期刊论文列表失败:', error);
      throw error;
    }
  }

  /**
   * 更新期刊论文信息
   * @param {number} paperId - 论文ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<number>} 影响的行数
   */
  async updatePaper(paperId, updateData) {
    try {
      // 验证日期格式
      if (updateData.publication_date && !this.isValidDate(updateData.publication_date)) {
        throw new Error('发表日期格式不正确，请使用 YYYY-MM-DD 格式');
      }

      // 验证引用次数为数字
      if (updateData.number_of_citations && isNaN(Number(updateData.number_of_citations))) {
        throw new Error('引用次数必须为数字');
      }

      // 过滤掉不允许更新的字段
      const allowedFields = [
        'title', 'author', 'author_ranking', 'keywords', 'journal_name',
        'journal_abbreviation', 'publication_date', 'volume_number',
        'page_numbers', 'jcr_quartile', 'sci_quartile', 'other_level',
        'field_of_expertise', 'impact_factor', 'number_of_citations',
        'paper_file_url', 'attachment_url'
      ];
      
      const filteredData = {};
      Object.keys(updateData).forEach(key => {
        if (allowedFields.includes(key)) {
          filteredData[key] = updateData[key];
        }
      });

      if (Object.keys(filteredData).length === 0) {
        throw new Error('没有有效的更新字段');
      }

      // 转换引用次数为数字
      if (filteredData.number_of_citations) {
        filteredData.number_of_citations = Number(filteredData.number_of_citations);
      }

      const changes = await this.db.update('journal_paper_info', paperId, filteredData);
      return changes;
    } catch (error) {
      console.error('更新期刊论文失败:', error);
      throw error;
    }
  }

  /**
   * 删除期刊论文
   * @param {number} paperId - 论文ID
   * @returns {Promise<number>} 影响的行数
   */
  async deletePaper(paperId) {
    try {
      const changes = await this.db.delete('journal_paper_info', paperId);
      return changes;
    } catch (error) {
      console.error('删除期刊论文失败:', error);
      throw error;
    }
  }

  /**
   * 根据作者搜索论文
   * @param {string} author - 作者姓名
   * @returns {Promise<Array>} 匹配的论文列表
   */
  async getPapersByAuthor(author) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `SELECT * FROM journal_paper_info WHERE author LIKE ? ORDER BY publication_date DESC`;
        this.db.db.all(sql, [`%${author}%`], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    } catch (error) {
      console.error('根据作者搜索论文失败:', error);
      throw error;
    }
  }

  /**
   * 根据期刊名称搜索论文
   * @param {string} journalName - 期刊名称
   * @returns {Promise<Array>} 匹配的论文列表
   */
  async getPapersByJournal(journalName) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `SELECT * FROM journal_paper_info WHERE journal_name LIKE ? ORDER BY publication_date DESC`;
        this.db.db.all(sql, [`%${journalName}%`], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    } catch (error) {
      console.error('根据期刊搜索论文失败:', error);
      throw error;
    }
  }

  /**
   * 根据专业领域搜索论文
   * @param {string} field - 专业领域
   * @returns {Promise<Array>} 匹配的论文列表
   */
  async getPapersByField(field) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `SELECT * FROM journal_paper_info WHERE field_of_expertise LIKE ? ORDER BY publication_date DESC`;
        this.db.db.all(sql, [`%${field}%`], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    } catch (error) {
      console.error('根据专业领域搜索论文失败:', error);
      throw error;
    }
  }

  /**
   * 综合搜索论文
   * @param {string} keyword - 搜索关键词
   * @returns {Promise<Array>} 匹配的论文列表
   */
  async searchPapers(keyword) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `
          SELECT * FROM journal_paper_info 
          WHERE title LIKE ? 
             OR author LIKE ? 
             OR keywords LIKE ? 
             OR journal_name LIKE ? 
             OR field_of_expertise LIKE ?
          ORDER BY publication_date DESC
        `;
        
        const searchPattern = `%${keyword}%`;
        this.db.db.all(sql, [searchPattern, searchPattern, searchPattern, searchPattern, searchPattern], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    } catch (error) {
      console.error('搜索论文失败:', error);
      throw error;
    }
  }

  /**
   * 获取论文统计信息
   * @returns {Promise<Object>} 统计信息
   */
  async getPaperStatistics() {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `
          SELECT 
            COUNT(*) as total_papers,
            COUNT(DISTINCT author) as unique_authors,
            COUNT(DISTINCT journal_name) as unique_journals,
            COUNT(DISTINCT field_of_expertise) as unique_fields,
            SUM(number_of_citations) as total_citations,
            AVG(number_of_citations) as avg_citations
          FROM journal_paper_info
        `;
        
        this.db.db.get(sql, [], (err, row) => {
          if (err) {
            reject(err);
          } else {
            resolve(row);
          }
        });
      });
    } catch (error) {
      console.error('获取论文统计信息失败:', error);
      throw error;
    }
  }

  /**
   * 根据年份范围获取论文
   * @param {string} startYear - 开始年份
   * @param {string} endYear - 结束年份
   * @returns {Promise<Array>} 论文列表
   */
  async getPapersByYearRange(startYear, endYear) {
    try {
      return new Promise((resolve, reject) => {
        if (!this.db.isInitialized) {
          reject(new Error('数据库未初始化'));
          return;
        }

        const sql = `
          SELECT * FROM journal_paper_info 
          WHERE strftime('%Y', publication_date) BETWEEN ? AND ?
          ORDER BY publication_date DESC
        `;
        
        this.db.db.all(sql, [startYear, endYear], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    } catch (error) {
      console.error('根据年份范围获取论文失败:', error);
      throw error;
    }
  }

  /**
   * 验证日期格式
   * @param {string} dateString - 日期字符串
   * @returns {boolean} 是否为有效日期
   */
  isValidDate(dateString) {
    const regex = /^\d{4}-\d{2}-\d{2}$/;
    if (!regex.test(dateString)) {
      return false;
    }
    
    const date = new Date(dateString);
    return date instanceof Date && !isNaN(date);
  }
}

module.exports = JournalPaperService;
